lyfz_repos/铂金版源码/DBFile/SQL/2015-08-13[收集]/2015-10-12(胡工).sql

if  exists
(select * from syscolumns where id=object_id('tb_ErpWageCommissionSet') and name='Wcs_Percentage') 
begin
ALTER TABLE tb_ErpWageCommissionSet ALTER COLUMN Wcs_Percentage decimal(18, 2) 
end


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_MonthlyReport')
BEGIN
	DROP VIEW [dbo].Vw_MonthlyReport
END 
GO
create View Vw_MonthlyReport
as
select 
Pay_CreateDatetimes
,(select sum(Pay_AmountOf) from tb_ErpPayment where (Pay_Category='全款' or Pay_Category='预约收款')  and  (Pay_PaymentMethod!='BEBACCAFEGECFBJFD' and Pay_PaymentMethod!='BEBCABAJBDFBBJGID')  and Pay_CreateDatetime>=Pay_CreateDatetimes and Pay_CreateDatetime<=Pay_CreateDatetimes+' 23:59:59.000') as DayOrdersIncome
, (select sum(Pay_AmountOf) from tb_ErpPayment where Pay_Category='预约补款'  and  (Pay_PaymentMethod!='BEBACCAFEGECFBJFD' and Pay_PaymentMethod!='BEBCABAJBDFBBJGID') and Pay_CreateDatetime>=Pay_CreateDatetimes and Pay_CreateDatetime<=Pay_CreateDatetimes+' 23:59:59.000') as DayReplenishmentIncome
 ,(select sum(Pay_AmountOf) from tb_ErpPayment where Pay_Category='后期收款'  and  (Pay_PaymentMethod!='BEBACCAFEGECFBJFD' and Pay_PaymentMethod!='BEBCABAJBDFBBJGID') and Pay_CreateDatetime>=Pay_CreateDatetimes and Pay_CreateDatetime<=Pay_CreateDatetimes+' 23:59:59.000') as DayLateStageIncome
, (select sum(Oiae_Money) from tb_ErpOtherIncomeAndExpenses where Oiae_Type='收入' and Oiae_PaymentMethod!='BEBACCAFEGECFBJFD' and Oiae_IEDatetime>=Pay_CreateDatetimes and Oiae_IEDatetime<=Pay_CreateDatetimes+' 23:59:59.000') as DayOtherIncome
,(select sum(Mcrr_RechargeAmount) from tb_ErpMemberCardRechargeRecord where Mcrr_CreateDatetime>=Pay_CreateDatetimes and Mcrr_CreateDatetime<=Pay_CreateDatetimes+' 23:59:59.000') as DayMemberIncome
,(select sum(Ord_SeriesPrice) from tb_ErpOrder where ord_class='1' and Ord_CreateDatetime>=Pay_CreateDatetimes and Ord_CreateDatetime<=Pay_CreateDatetimes+' 23:59:59.000') as DayEarlyPerformance
,(select sum(Plu_Amount) from tb_ErpPlusPickItems where Plu_CreateTime>=Pay_CreateDatetimes and Plu_CreateTime<=Pay_CreateDatetimes+' 23:59:59.000') as  DayPluslatepickPerformance 
,(select sum(Tsorder_Money) from tb_ErpTwoSalesOrder where Tsorder_CreateDatetime>=Pay_CreateDatetimes and Tsorder_CreateDatetime<=Pay_CreateDatetimes+' 23:59:59.000') as DayOtherPerformance
, (select sum(Oiae_Money) from tb_ErpOtherIncomeAndExpenses where (Oiae_Type='支出')  and Oiae_IEDatetime>=Pay_CreateDatetimes and Oiae_IEDatetime<=Pay_CreateDatetimes+' 23:59:59.000') as DayExpenditure
, (select sum(Oiae_Money) from tb_ErpOtherIncomeAndExpenses where (Oiae_Type='财务支出')  and Oiae_IEDatetime>=Pay_CreateDatetimes and Oiae_IEDatetime<=Pay_CreateDatetimes+' 23:59:59.000') as DayExpenditure2
,(select sum(Mcpt_PaymentAmount) from tb_ErpMemberCardPayment where Mcpt_CreateDatetime>=Pay_CreateDatetimes and Mcpt_CreateDatetime<=Pay_CreateDatetimes+' 23:59:59.000') as DayMemberCardPaymentIncome
 from 
 (
  select Pay_CreateDatetimes from  Vw_ReportTime
 ) as MonthlyReport
group by Pay_CreateDatetimes

GO



IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_YearReport')
BEGIN
	DROP VIEW [dbo].Vw_YearReport
END 
GO
create View Vw_YearReport
as
select 
Pay_CreateDatetimes
,(select sum(Pay_AmountOf) from tb_ErpPayment where (Pay_Category='全款' or Pay_Category='预约收款')  and (Pay_PaymentMethod!='BEBACCAFEGECFBJFD' and Pay_PaymentMethod!='BEBCABAJBDFBBJGID')  and convert(varchar,Pay_CreateDatetime,120) like '%'+Pay_CreateDatetimes+'%') as DayOrdersIncome
, (select sum(Pay_AmountOf) from tb_ErpPayment where Pay_Category='预约补款'  and (Pay_PaymentMethod!='BEBACCAFEGECFBJFD' and Pay_PaymentMethod!='BEBCABAJBDFBBJGID') and convert(varchar,Pay_CreateDatetime,120) like '%'+Pay_CreateDatetimes+'%') as DayReplenishmentIncome
,(select sum(Pay_AmountOf) from tb_ErpPayment where Pay_Category='后期收款'  and (Pay_PaymentMethod!='BEBACCAFEGECFBJFD' and Pay_PaymentMethod!='BEBCABAJBDFBBJGID') and convert(varchar,Pay_CreateDatetime,120) like '%'+Pay_CreateDatetimes+'%') as DayLateStageIncome
, (select sum(Oiae_Money) from tb_ErpOtherIncomeAndExpenses where Oiae_Type='收入' and Oiae_PaymentMethod!='BEBACCAFEGECFBJFD' and convert(varchar,Oiae_IEDatetime,120) like '%'+Pay_CreateDatetimes+'%') as DayOtherIncome
,(select sum(Mcrr_RechargeAmount) from tb_ErpMemberCardRechargeRecord where convert(varchar,Mcrr_CreateDatetime,120) like '%'+Pay_CreateDatetimes+'%') as DayMemberIncome
,(select sum(Ord_SeriesPrice) from tb_ErpOrder where ord_class='1' and convert(varchar,Ord_CreateDatetime,120) like '%'+Pay_CreateDatetimes+'%') as DayEarlyPerformance
,(select sum(Plu_Amount) from tb_ErpPlusPickItems where convert(varchar,Plu_CreateTime,120) like '%'+Pay_CreateDatetimes+'%') as  DayPluslatepickPerformance 
,(select sum(Tsorder_Money) from tb_ErpTwoSalesOrder where convert(varchar,Tsorder_CreateDatetime,120) like '%'+Pay_CreateDatetimes+'%') as DayOtherPerformance
, (select sum(Oiae_Money) from tb_ErpOtherIncomeAndExpenses where (Oiae_Type='支出')   and convert(varchar,Oiae_IEDatetime,120) like '%'+Pay_CreateDatetimes+'%') as DayExpenditure
, (select sum(Oiae_Money) from tb_ErpOtherIncomeAndExpenses where (Oiae_Type='财务支出')   and convert(varchar,Oiae_IEDatetime,120) like '%'+Pay_CreateDatetimes+'%') as DayExpenditure2
,(select sum(Mcpt_PaymentAmount) from tb_ErpMemberCardPayment where convert(varchar,Mcpt_CreateDatetime,120) like '%'+Pay_CreateDatetimes+'%') as DayMemberCardPaymentIncome
 from 
 (
     select convert(varchar(7),Pay_CreateDatetimes,120)  as Pay_CreateDatetimes from Vw_ReportTime
  ) as YearReport
group by Pay_CreateDatetimes

GO



 if not exists
(select * from syscolumns where id=object_id('tb_ErpDressSaleRentalOrder') and name='Dsro_HandledName') 
begin
alter table tb_ErpDressSaleRentalOrder add Dsro_HandledName nvarchar(20) 
end


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'View_DressSaleRentalOrder')
BEGIN
	DROP VIEW [dbo].View_DressSaleRentalOrder
END 
GO
create View View_DressSaleRentalOrder
as
SELECT tb_ErpDressSaleRentalOrder.ID,Dsro_Number,Dsro_Type,Dsro_Amount,Dsro_MortgageAmount,Dsro_CustomerNumber
,Dsro_TakeDressTime,Dsro_ReservationBackTime,Dsro_Remark,Dsro_CreateDateTime,Dsro_CreateName,Dsro_UpdateDateTime,
Dsro_UpdateName,Cus_CustomerNumber,Cus_CustomizeNumber,Cus_Type,Cus_ServiceType,Cus_Grade,Cus_Name,Cus_Sex,Cus_Birthday
,Cus_BirthdayLunar,Cus_DayForMarriage,Cus_DayForMarriageLunar,Cus_Relations,Cus_QQ,Cus_MicroSignal,Cus_Telephone,Cus_FixedPhone,
Cus_Region,Cus_Address,Cus_WorkUnit,Cus_BabyWeight,Cus_BornHospital,Cus_Zodiac,Cus_CustomerSource,Cus_Status,Cus_LossReason,
Cus_DegreeOfIntent,Cus_TrackName,Cus_Remark,Cus_CreateDateTime,Cus_CreateName,Cus_UpdateDateTime,Cus_UpdateName,dbo.tb_Product(Cus_Name) as Py_Cus_Name
  ,dbo.fn_CheckUserIDGetUserName(Dsro_CreateName) as 开单人姓名
  ,dbo.fn_CheckUserIDGetUserName(Dsro_HandledName) as 经手人
 FROM tb_ErpDressSaleRentalOrder left join tb_ErpCustomer on Dsro_CustomerNumber=Cus_CustomerNumber

GO



IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'View_DressSaleRentalDetail')
BEGIN
	DROP VIEW [dbo].View_DressSaleRentalDetail
END 
GO
create View View_DressSaleRentalDetail
as
SELECT   tb_ErpDressSaleRentalDetail.Id,Dsrd_Number,Dsrd_DressNumber,Dsrd_DressName,Dsrd_SaleRentalQuantity,Dsrd_CostPrice,
Dsrd_SalePrice,Dsrd_BackTime,Dsrd_BackStatus,Dsrd_BackRemark,Dsrd_CreateDateTime,Dsrd_CreateName,Dsrd_UpdateDateTime,
Dsrd_UpdateName,Dsro_Number,Dsro_Type,Dsro_Amount,Dsro_MortgageAmount,Dsro_CustomerNumber,Dsro_TakeDressTime,
Dsro_ReservationBackTime,Dsro_Remark,Dsro_CreateDateTime,Dsro_CreateName,Dsro_UpdateDateTime,Dsro_UpdateName,Cus_Name ,Dsfm_RentPrice
FROM tb_ErpDressSaleRentalDetail left join tb_ErpDressSaleRentalOrder on Dsrd_Number=Dsro_Number  
left join tb_ErpCustomer on Dsro_CustomerNumber=Cus_CustomerNumber 
left join tb_ErpDressFrom on Dsrd_DressNumber=Dsfm_DressNumber

GO